#include "server_fun.h"

int flag_passwd = 0;//密码标志位
int flag_find = 0;//
static int accpetfd;//存储用于通信套接字
static char name[24];//记录进行操作的用户名
static char *temp;//记录更新操作的列名

//数据库初始化函数
//参数:数据库名
//返回值:指向sqlite句柄的指针,初始化失败,返回NULL
sqlite3* sqlite3_init(char *name)
{
	sqlite3 *db;
	char *errmsg = NULL;
	int errnum = 0;
	//打开数据库
	if(sqlite3_open(name,&db) < 0){
		fprintf(stderr, "sqlite3 open err: %s\n",sqlite3_errmsg(db));
		return NULL;
	}
	printf("open sqlite is ok.\n");
	//创建表
	//创建数据表
	errnum = sqlite3_exec(db,"CREATE TABLE usrinfo(staffno integer PRIMARY KEY,usertype integer,name text UNIQUE,passwd text,age integer,phone text,addr text,work text,date text,level integer,salary REAL);", NULL, NULL,&errmsg);
	if(errnum != 0){
		if(errnum == SQLITE_ERROR){
			printf("usrinfo is exist.\n");
		}else{
			fprintf(stderr,"sqlite3 creat table usrinfo err: %s\n",errmsg);
			return NULL;
		}
	}else{
		printf("creat usrinfo is ok.\n");
	}
	//创建历史表
	errnum = sqlite3_exec(db,"CREATE TABLE historyinfo(time text,name text,words text);", NULL, NULL,&errmsg);
	if(errnum != 0){
		if(errnum == SQLITE_ERROR){
			printf("historyinfo is exist.\n");
		}else{
			fprintf(stderr,"sqlite3 creat table historyinfo err: %s\n",errmsg);
			return NULL;
		}
	}else{
		printf("creat historyinfo is ok.\n");
	}
	//创建初始管理员admin用户
	errnum = sqlite3_exec(db,"insert into usrinfo values (0,0,'admin','admin',?,?,?,?,?,?,?);", NULL, NULL,&errmsg);
	if(errnum != 0){
		if(errnum == SQLITE_CONSTRAINT){
			printf("admin is exist.\n");
		}else{
			fprintf(stderr,"sqlite3 creat admin err: %s\n",errmsg);
			return NULL;
		}
	}else{
		printf("creat admin is ok.\n");
	}
	return db;
}

//数据接收函数
//参数:用于通信的套接字,通信结构体指针
int recv_msg_server_fun(int socketfd,TranMsg *tranmsg)
{
	accpetfd = socketfd;//拓展通信用的套接字的生命周期
	sprintf(name,"%s",tranmsg->usrinfo.name);//保存用户名,供历史记录函数使用
	printf("name = %s\npasswd = %s\n",tranmsg->usrinfo.name,tranmsg->usrinfo.passwd);
	printf("usertype = %d\ncmdtype = %d\n",tranmsg->usertype,tranmsg->cmdtype);
	switch(tranmsg->cmdtype)//判断指令
	{
		case log://登录功能
			tranmsg->cmdtype = log_server(tranmsg);//执行登录函数,写入操作指令
			send(accpetfd, tranmsg, sizeof(TranMsg),0);//发送
			break;
		case find:
		case find_all:
		case find_id://查找功能
			if(find_server(tranmsg) == FIND_NOT){			
				tranmsg->cmdtype = FIND_NOT;
			}else{
				tranmsg->cmdtype = FIND_OVER;
			}
			send(accpetfd, tranmsg, sizeof(TranMsg),0);
			break;
		case update_addr:
		case update_phone:
		case update_passwd:
		case update_name:
		case update_age:
		case update_work:
		case update_date:
		case update_level:
		case update_salary://更新数据
			tranmsg->cmdtype = update_server(tranmsg);	
			//更新成功的话,记录操作
			if(tranmsg->cmdtype == UPDATE_SUCCEED){
				history_server(tranmsg);
			}
			send(accpetfd, tranmsg, sizeof(TranMsg),0);
			break;
		case add://添加用户
			tranmsg->cmdtype = add_server(tranmsg);	
			if(tranmsg->cmdtype == ADD_SUCCEED){
				history_server(tranmsg);
			}
			send(accpetfd, tranmsg, sizeof(TranMsg),0);
			break;
		case delet://删除用户
			tranmsg->cmdtype = delet_server(tranmsg);	
			if(tranmsg->cmdtype == DELETE_SUCCEED){
				history_server(tranmsg);
			}
			send(accpetfd, tranmsg, sizeof(TranMsg),0);
			break;
		case history://获取历史记录
			if( get_history_server(tranmsg) == HISTORY_SUCCEED){
				tranmsg->cmdtype = HISTORY_OVER;
			}else{
				tranmsg->cmdtype = HISTORY_FAILE;
			}	
			send(accpetfd, tranmsg, sizeof(TranMsg),0);
			break;
		default :
			break;
	}
}

//登录callback
int callback_login(void *arg, int f_num, char **f_value, char **f_name)
{
	//判断用户名的原始权限是否与操作权限相等且是否有管理员权限
	if(((TranMsg *)arg)->usertype != *f_value[1]-'0' && *f_value[1] == '1'){
		return -1;
	}
	/*校验密码*/
	if(strncmp(((TranMsg *)arg)->usrinfo.passwd, f_value[3], strlen(f_value[3])) == 0){
		printf("passwd turn.\n");
		flag_passwd = 1;		/*标志位置一,表明密码对*/
		((TranMsg*)arg)->usrinfo.staffno = *f_value[0]-'0';//密码正确后,填充结构体的工号和姓名
		sprintf(((TranMsg*)arg)->usrinfo.name,"%s",f_value[2]);
	}
	return 1;
}

//登录函数
//参数:通信结构体指针
int log_server(TranMsg *tranmsg)
{
	char sql[128] = {0};
	char *errmsg;
	//拼接sql语句
	sprintf(sql,"select * from usrinfo where name='%s';",tranmsg->usrinfo.name);
	printf("%s\n",sql);
	//执行sql语句,在callback函数中校验密码
	int err = sqlite3_exec(db, sql, callback_login, tranmsg, &errmsg);
	printf("err = %d\n",err);
	//根据返回值返回登录结果
	if(err == 0){
		return USR_UNEXIST;
	}else if(err == SQLITE_ABORT){
		//判断密码是否正确
		if(flag_passwd == 1){
			flag_passwd = 0;//标志位复位
			printf("usr login succeed.\n");
			return USR_LOGIN_SUCCEED;
		}else{
			printf("usr name or passwd err, login failed.\n");
			return USR_ERR_INFORMATION;
		}
	}else{
		fprintf(stderr,"sqlite3 exec select err: %s\n",errmsg);
		return err;
	}
}

//查找callback
int callback_find(void *arg, int f_num, char **f_value, char **f_name)
{
	int i;
	memset(((TranMsg*)arg)->buf,0,sizeof(((TranMsg*)arg)->buf));
	if(flag_find == 0){//保证表头只打印一遍
		for(i=0; i<f_num; i++)
		{
			sprintf(((TranMsg *)arg)->buf,"%s\t%s",((TranMsg *)arg)->buf,f_name[i]);
		}
		sprintf(((TranMsg *)arg)->buf,"%s%c",((TranMsg *)arg)->buf,'\n');
		printf("buf = %s\n",((TranMsg*)arg)->buf);
		printf("cmdtype = %d\n",((TranMsg*)arg)->cmdtype);
		send(accpetfd,(TranMsg*)arg,sizeof(TranMsg),0);
		memset(((TranMsg*)arg)->buf,0,sizeof(((TranMsg*)arg)->buf));
	}
	//打印详细信息
	for(i=0; i<f_num; i++)
	{
		sprintf(((TranMsg*)arg)->buf,"%s\t%s",((TranMsg*)arg)->buf,f_value[i]);
	}
	sprintf(((TranMsg *)arg)->buf,"%s%c",((TranMsg *)arg)->buf,'\n');
	printf("buf = %s\n",((TranMsg*)arg)->buf);
	printf("cmdtype = %d\n",((TranMsg*)arg)->cmdtype);
	send(accpetfd,(TranMsg*)arg,sizeof(TranMsg),0);//发送
	memset(((TranMsg*)arg)->buf,0,sizeof(((TranMsg*)arg)->buf));//清空
	flag_find = 1;//查找标志位置一,表示查询到了数据
	return 0;
}

//查找函数
//参数:通信结构体指针
int find_server(TranMsg *tranmsg)
{
	char sql[128] = {0};
	char *errmsg;
	//判断权限
	if(tranmsg->usertype == 0){
		if(tranmsg->cmdtype == find_all){
			sprintf(sql,"select * from usrinfo;");
			printf("%s\n",sql);
		}else if(tranmsg->cmdtype == find_id){
			printf("buf[1] = %d\n",tranmsg->buf[1]);
			sprintf(sql,"select * from usrinfo where staffno = '%d';",tranmsg->usrinfo.staffno);
			printf("%s\n",sql);
		}
	}else{
		sprintf(sql,"select * from usrinfo where staffno = '%d';",tranmsg->usrinfo.staffno);
		printf("%s\n",sql);
	}
	int err = sqlite3_exec(db, sql, callback_find, tranmsg, &errmsg);
	printf("err = %d\n",err);
	if(err == 0){
		if(flag_find == 1){
			flag_find = 0;//复位查找标志位
			return FIND_SUCCEED; 
		}else{
			return FIND_NOT;
		}
	}
}

//更新数据callback
int callback_update(void *arg, int f_num, char **f_value, char **f_name)
{
	char sql[128] = {0};
	char *errmsg;
	flag_find = 1;//查找标志位置一,表示查询到了数据
	sprintf(sql,"update usrinfo set %s = '%s' where staffno = '%d';",temp,((TranMsg *)arg)->buf,((TranMsg *)arg)->usrinfo.staffno);
	printf("%s\n",sql);
	int err = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	printf("err = %d\n",err);
	return 0;
}

//更新数据函数
//参数:通信结构体指针
int update_server(TranMsg *tranmsg)
{
	char sql[128] = {0};
	char *errmsg;
	//根据指令确定操作数据库中的哪一列
	if(tranmsg->cmdtype == update_addr){
		temp = "addr";
	}else if(tranmsg->cmdtype == update_phone){
		temp = "phone";
	}else if(tranmsg->cmdtype == update_passwd){
		temp = "passwd";
	}else if(tranmsg->cmdtype == update_name){
		temp = "name";
	}else if(tranmsg->cmdtype == update_age){
		temp = "age";
	}else if(tranmsg->cmdtype == update_work){
		temp = "work";
	}else if(tranmsg->cmdtype == update_date){
		temp = "date";
	}else if(tranmsg->cmdtype == update_level){
		temp = "level";
	}else if(tranmsg->cmdtype == update_salary){
		temp = "salary";
	}
	sprintf(sql,"select * from usrinfo where staffno = '%d';",tranmsg->usrinfo.staffno);
	printf("%s\n",sql);
	int err = sqlite3_exec(db, sql, callback_update, tranmsg, &errmsg);
	printf("err = %d\n",err);
	if(err == 0){
		if(flag_find == 1){
			flag_find == 0;
			return UPDATE_SUCCEED;
		}else{
			return UPDATE_FAILE;
		}
	}else{
		return UPDATE_FAILE;
	}
}

//添加新用户函数
//参数:通信结构体指针
int add_server(TranMsg *tranmsg)
{
	char sql[128] = {0};
	char *errmsg;
	sprintf(sql,"insert into usrinfo values (%d,%d,'%s','%s',?,?,?,?,?,?,?);",tranmsg->usrinfo.staffno,tranmsg->usrinfo.usertype,tranmsg->buf,tranmsg->usrinfo.passwd);
	printf("%s\n",sql);
	int err = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	printf("err = %d\n",err);
	if(err == 0){
		return ADD_SUCCEED;
	}else{
		return ADD_FAILE;
	}

}

//删除callback
int callback_delete(void *arg, int f_num, char **f_value, char **f_name)
{
	flag_find = 1;
	char sql[128] = {0};
	char *errmsg;
	sprintf(sql,"delete from usrinfo where staffno = %d;",((TranMsg*)arg)->usrinfo.staffno);
	printf("%s\n",sql);
	sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	return 0;
}

//删除用户函数
//参数:通信结构体指针
int delet_server(TranMsg *tranmsg)
{
	char sql[128] = {0};
	char *errmsg;
	sprintf(sql,"select * from usrinfo where staffno = %d;",tranmsg->usrinfo.staffno);
	printf("%s\n",sql);
	int err = sqlite3_exec(db, sql, callback_delete, tranmsg, &errmsg);
	printf("err = %d\n",err);
	if(err == 0){
		if(flag_find == 1){
			flag_find == 0;
			return DELETE_SUCCEED;
		}else{
			return DELETE_FAILE;
		}
	}else{
		return DELETE_FAILE;
	}

}

//查询历史记录函数callback
int callback_history(void *arg, int f_num, char **f_value, char **f_name)
{
	int i;
	for(i=0; i<f_num; i++)
	{
		sprintf(((TranMsg*)arg)->buf,"%s\t%s",((TranMsg*)arg)->buf,f_value[i]);
	}
	sprintf(((TranMsg *)arg)->buf,"%s%c",((TranMsg *)arg)->buf,'\n');
	printf("buf = %s\n",((TranMsg*)arg)->buf);
	printf("cmdtype = %d\n",((TranMsg*)arg)->cmdtype);
	send(accpetfd,(TranMsg*)arg,sizeof(TranMsg),0);
	memset(((TranMsg*)arg)->buf,0,sizeof(((TranMsg*)arg)->buf));
	return 0;
}

//查询历史记录函数
//参数:通信结构体指针
int get_history_server(TranMsg *tranmsg)
{
	char sql[128] = {0};
	char *errmsg;
	sprintf(sql,"select * from historyinfo;");
	printf("%s\n",sql);
	int err = sqlite3_exec(db, sql, callback_history, tranmsg, &errmsg);
	printf("err = %d\n",err);
	if(err == 0){
		return HISTORY_SUCCEED;
	}else{
		return HISTORY_FAILE;
	}

}

//操作内容记录函数
//参数:通信结构体指针
void history_word_server(TranMsg *tranmsg,char * word)
{
	if(tranmsg->usertype == 0){
		sprintf(word,"[%s]%s","管理员",name);
	}else{
		sprintf(word,"[%s]%s","普通用户",name);
	}
	if(tranmsg->cmdtype == UPDATE_SUCCEED){
		sprintf(word,"%s %s %d %s %s %s",word,"修改工号",tranmsg->usrinfo.staffno,temp,"为",tranmsg->buf);
	}
	if(tranmsg->cmdtype == ADD_SUCCEED){
		sprintf(word,"%s %s %d %s %s",word,"添加新用户,工号为",tranmsg->usrinfo.staffno,"姓名为",tranmsg->buf);
	}
	if(tranmsg->cmdtype == DELETE_SUCCEED){
		sprintf(word,"%s %s %d %s",word,"删除工号为",tranmsg->usrinfo.staffno,"的用户");
	}
}

//历史记录函数
//参数:通信结构体指针
int history_server(TranMsg *tranmsg)
{
	char buf[80] = {0};
	time_t k;
	struct tm *tm;
	char sql[128] = {0};
	char *errmsg;
	/*获取系统时间*/
	time(&k);
	tm=localtime(&k);
	/*转换时间格式*/
	strftime(buf, 80, "%Y-%m-%d %H:%M:%S", tm);
	printf("strftime: %s\n", buf);
	/*写入数据库*/
	char word[128] = {0};
	history_word_server(tranmsg,word);
	sprintf(sql,"insert into historyinfo values('%s','%s','%s');", buf, name, word);
	printf("%s\n", sql);
	int err = sqlite3_exec(db, sql, NULL, NULL, &errmsg);
	if(err != 0){
		fprintf(stderr, "sqlite3 exec insert err: %s\n", errmsg);
		return err;
	}
	return 0;
}
